package simorg.domain.persistence;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import simorg.util.DatabaseConnection;

public class StudentCourseJoinTable {

	public static void bootstrap(boolean drop) throws Exception {
		DatabaseConnection dc = new DatabaseConnection();
		try {
			Statement stmt = null;
		    String query = null;
		    if ( drop ) {
		       query = "DROP TABLE IF EXISTS STUDENT_COURSE";
		    	stmt = dc.getConnection().createStatement();
			    stmt.execute(query);
		    }
		    query = "CREATE TABLE IF NOT EXISTS STUDENT_COURSE" +
	    	" (USER_ID INT, COURSE_ID INT, PRIMARY KEY (USER_ID, COURSE_ID), " +
	    	" FOREIGN KEY (USER_ID) REFERENCES USER(ID), " +
	    	" FOREIGN KEY (COURSE_ID) REFERENCES COURSE(ID))";
		    stmt = dc.getConnection().createStatement();
		    stmt.execute(query); 
		}
		finally {
			dc.getConnection().close();
		}
	}
	
	public static boolean isEnrolled(DatabaseConnection dc, int studentId, int courseId) {
		String query = "SELECT * FROM STUDENT_COURSE " + 
		"WHERE USER_ID=? AND COURSE_ID=?";
		try {
			PreparedStatement stmt = dc.getConnection().prepareStatement(query);
			stmt.setInt(1, studentId);
			stmt.setInt(2, courseId);
			ResultSet rs = stmt.executeQuery();
			return rs.next();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	public static void enroll(DatabaseConnection dc, int studentId, int courseId) {
		if ( isEnrolled(dc, studentId, courseId) ) {
			return ;
		}
		try {
			String query = "INSERT INTO STUDENT_COURSE (USER_ID, COURSE_ID ) " +
			"VALUES ( ?, ?)";
			PreparedStatement insert = dc.getConnection().prepareStatement(query);
			insert.setInt(1, studentId);
			insert.setInt(2, courseId);
			insert.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return;
		}
	}
	
	public static void remove(DatabaseConnection dc, int studentId, int courseId) {
		if ( !isEnrolled(dc, studentId, courseId) ) {
			return ;
		}
		try {
			String query = "DELETE STUDENT_COURSE WHERE USER_ID=? AND COURSE_ID=?";
			PreparedStatement remove = dc.getConnection().prepareStatement(query);
			remove.setInt(1, studentId);
			remove.setInt(2, courseId);
			remove.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public static Collection<Integer> getCourseIds(DatabaseConnection dc, int studentId) {
		ArrayList<Integer> retval = new ArrayList<Integer>();
		try {
			String query = "SELECT COURSE_ID FROM STUDENT_COURSE " + 
				"WHERE USER_ID=?";
			PreparedStatement stmt = dc.getConnection().prepareStatement(query);
			stmt.setInt(1, studentId);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()){
				retval.add(rs.getInt("COURSE_ID"));
		    }
		} catch (Exception e) {
			e.printStackTrace();
		}
		return retval;
	}
	public static Collection<Integer> getUserIds(DatabaseConnection dc, int courseId) {
		ArrayList<Integer> retval = new ArrayList<Integer>();
		try {
			String query = "SELECT USER_ID FROM STUDENT_COURSE " + 
				"WHERE COURSE_ID=?";
			PreparedStatement stmt = dc.getConnection().prepareStatement(query);
			stmt.setInt(1, courseId);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()){
				retval.add(rs.getInt("USER_ID"));
		    }
		} catch (Exception e) {
			e.printStackTrace();
		}
		return retval;
	}
}
